using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using GisSharpBlog.NetTopologySuite.Geometries;
using MsSqlSpatial;

public partial class Triggers
{
	internal const string GeometryTableTriggerMethodName = "GeometryTableTrigger";
	/// <summary>
	/// Updates envelope geometry columns for updates and inserts on geometry tables.
	/// This trigger will be associated to every table with geometry columns
	/// and will be disassociated when no geometry column remains in the table.
	/// </summary>
	public static void GeometryTableTrigger()
	{
		SqlTriggerContext triggerContext = SqlContext.TriggerContext;

		// Get schema and table names.
		string schemaName = null;
		string tableName = null;

		if (triggerContext.EventData == null)
			throw new ApplicationException("triggerContext.EventData == null");
		XmlReader eventReader = triggerContext.EventData.CreateReader();
		if (eventReader == null)
			throw new ApplicationException("eventReader == null");
		while (eventReader.Read())
		{
			if (eventReader.Name.Equals("SchemaName"))
				schemaName = eventReader.ReadElementContentAsString();
			else
			if (eventReader.Name.Equals("ObjectName"))
				tableName = eventReader.ReadElementContentAsString();
		}

		Dictionary<string, int> columns = new Dictionary<string, int>();
		using (SqlConnection connection = new SqlConnection("context connection=true"))
		{
			connection.Open();
			using (SqlCommand command = connection.CreateCommand())
			{
				// Init common parameters
				command.Parameters.Add("@tableSchema", SqlDbType.NVarChar);
				command.Parameters.Add("@tableName", SqlDbType.NVarChar);
				command.Parameters[0].Value = schemaName;
				command.Parameters[1].Value = tableName;

				// Get table geometry columns
				command.CommandText =
@"SELECT [F_GEOMETRY_COLUMN]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @tableSchema
AND [F_TABLE_NAME] = @tableName";
				using (SqlDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						columns.Add(reader.GetString(0), 0);
					}
				}

				// Get columns ordinal position
				string[] columnNames = new string[] { };
				columns.Keys.CopyTo(columnNames, 0);
				command.CommandText = string.Format(
@"SELECT [COLUMN_NAME], [ORDINAL_POSITION]
FROM [INFORMATION_SCHEMA].[COLUMNS]
WHERE [TABLE_SCHEMA] = @tableSchema
AND [TABLE_NAME] = @tableName
AND [COLUMN_NAME] IN ({0})"
, String.Join(",", columnNames));
				using (SqlDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						columns[reader.GetString(0)] = reader.GetInt32(1);
					}
				}

				// Update geometry envelope columns
				command.Parameters.Clear();
				foreach (KeyValuePair<string, int> column in columns)
				{
					if (triggerContext.IsUpdatedColumn(column.Value))
					{
						string columnName = column.Key;

						// Get geometry
						command.CommandText = string.Format(
@"SELECT {0} FROM INSERTED"
, columnName);
						SqlBytes ewkb = command.ExecuteScalar() as SqlBytes;
						//SqlBinary ewkb = command.ExecuteScalar() as SqlBinary;
						command.Parameters.Add("@Envelope_MinX", SqlDbType.Float);
						command.Parameters.Add("@Envelope_MinY", SqlDbType.Float);
						command.Parameters.Add("@Envelope_MaxX", SqlDbType.Float);
						command.Parameters.Add("@Envelope_MaxY", SqlDbType.Float);
						GeometrySRIDPair pair = new GeometrySRIDPair(ewkb);

						// Update envelope
						command.CommandText = string.Format(
@"UPDATE [{0}].[{1}] SET
 [{2}_Envelope_MinX] = @Envelope_MinX
,[{2}_Envelope_MinY] = @Envelope_MinY
,[{2}_Envelope_MaxX] = @Envelope_MaxX
,[{2}_Envelope_MaxY] = @Envelope_MaxY
FROM INSERTED
INNER JOIN [{0}].[{1}]
ON INSERTED.[oid] = [{0}].[{1}].[oid]"
, schemaName, tableName, columnName);
						if (pair.HasGeometry)
						{
							Envelope envelope = pair.Geometry.EnvelopeInternal;
							command.Parameters["@Envelope_MinX"].Value = envelope.MinX;
							command.Parameters["@Envelope_MinY"].Value = envelope.MinY;
							command.Parameters["@Envelope_MaxX"].Value = envelope.MaxX;
							command.Parameters["@Envelope_MaxY"].Value = envelope.MaxY;
						}
						else
						{
							command.Parameters["@Envelope_MinX"].Value = null;
							command.Parameters["@Envelope_MinY"].Value = null;
							command.Parameters["@Envelope_MaxX"].Value = null;
							command.Parameters["@Envelope_MaxY"].Value = null;
						}
						command.ExecuteNonQuery();
					}
				}
			}
		}
	}
}



//        internal static void ExecuteCreateTrigger(SqlCommand command, string schemaName, string tableName)
//        {
//            string triggerName = BuildTriggerName(tableName);

//            if (!DatabaseHelper.CheckQueryExists(command, string.Format(
//@"SELECT object_id
//FROM sys.triggers 
//WHERE name = N'{0}'"
//, triggerName)))
//            {
//                Type type = typeof(Triggers);
//                command.CommandText = string.Format(
//@"CREATE TRIGGER [{0}].[{1}] 
//ON [{0}].[{2}]
//WITH EXECUTE AS CALLER 
//FOR UPDATE, INSERT 
//AS EXTERNAL NAME [{3}].[{4}{5}].[{6}]"
//, schemaName
//, triggerName
//, tableName
//, type.Assembly.GetName().Name
//, (type.Namespace == null ? "" : type.Namespace + ".")
//, type.Name
//, Triggers.GeometryTableTriggerMethodName);
//                command.ExecuteNonQuery();
//            }

//        }

//        internal static void ExecuteDropTrigger(SqlCommand command, string schemaName, string tableName)
//        {
//            string triggerName = BuildTriggerName(tableName);

//            if (DatabaseHelper.CheckQueryExists(command, string.Format(
//@"SELECT object_id
//FROM sys.triggers 
//WHERE name = N'{0}'"
//, triggerName))
//            /// Drop trigger only when no geometry column remains in the table.
//            && GeometryColumnsTable.GetGeometryColumnsByTable(command, schemaName, tableName).Count > 0)
//            {
//                Type type = typeof(Triggers);
//                command.CommandText = string.Format(
//@"DROP TRIGGER [{0}].[{1}]"
//, schemaName, triggerName);
//                command.ExecuteNonQuery();
//            }
//        }
